package org.example.hypersonaltools.sqlite;

import lombok.Getter;
import lombok.Setter;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TestTableSqlImpl {

    @Getter @Setter
    private Connection con;

    private final String tableName = "test";

    public TestTableSqlImpl(Connection connection) {
        con = connection;
        checkAndCreatTable();
    }

    private void checkAndCreatTable() {
        String sql = "CREATE TABLE IF NOT EXISTS test (\n"
                + " id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + " path TEXT NOT NULL,\n"
                + " iconImageData BLOB\n"
                + ");";
        try (Statement stmt = con.createStatement()) {
            // 执行创建表的SQL语句
            stmt.execute(sql);
            System.out.println("表已成功创建或已存在");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    // 插入数据
    public boolean add(String path, byte[] iconImageData) {
        String sql = "INSERT INTO " + tableName + " (path, iconImageData) VALUES (?, ?)";
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            pstmt.setString(1, path);
            pstmt.setBytes(2, iconImageData);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 删除数据
    public boolean delete(int id) {
        String sql = "DELETE FROM " + tableName + " WHERE id = ?";
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 更新数据
    public boolean update(int id, String path, byte[] iconImageData) {
        String sql = "UPDATE " + tableName + " SET path = ?, iconImageData = ? WHERE id = ?";
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            pstmt.setString(1, path);
            pstmt.setBytes(2, iconImageData);
            pstmt.setInt(3, id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 查询数据
    public List<iconData> query() {
        String sql = "SELECT id, path, iconImageData FROM " + tableName;
        try (PreparedStatement pstmt = con.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            List<iconData> list = new ArrayList<>();
            while (rs.next()) {
                iconData iconData = new iconData(rs.getInt(1), rs.getString(2), rs.getBytes(3));
                list.add(iconData);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    @Getter
    @Setter
    public static class iconData{
        private int id;
        private String path;
        private byte[] iconImageData;
        public iconData(int id, String path, byte[] iconImageData) {
            this.id = id;
            this.path = path;
            this.iconImageData = iconImageData;
        }
    }
}
